﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Collections;



public partial class RuleExecutionResult : System.Web.UI.Page
{
    int RuleID, CycleID;
    DataTable salesdata;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            RuleID = Convert.ToInt32(Request.QueryString["RuleID"].ToString());
            CycleID = Convert.ToInt32(Request.QueryString["CycleID"].ToString());
            salesdata = new DataTable();

            if (RuleID > 0)
            {
                retrieveDetails(RuleID, CycleID);
                //fill_result(RuleID);
            }
        }
    }

    private void retrieveDetails(int rule, int cycle)
    {
        DataTable subrule = getSubRules(rule);
        DateTime[] dateRange = getDateRange(rule, cycle);
        getSalesData(dateRange);

        foreach (DataRow dr in subrule.Rows)
        {
            int subRuleId = Convert.ToInt32(dr[0]);
            String selectQ = "select [WhenPart],[ThenPart] from [FormulaTable] where [id] = '" + subRuleId + "'";
            DataTable completeQuery = DataBaseMethods.ExecuteSelectQuery(selectQ);
            string whenpart = completeQuery.Rows[0][0].ToString();
            string thenpart = completeQuery.Rows[0][1].ToString();
            whenpart = generateSqlQuery(whenpart);
            thenpart = generateSqlQuery(thenpart);
            string frompart = "";

            string query = "select " + thenpart + " from " + frompart + " where " + whenpart;
        }
    }

    private string generateSqlQuery(string input)
    {
        string output = input.Replace("@", " ").Replace("\r\n", "").Replace("\"", "'");
        string key_value = "select ('$' + FormulaName + '()') as name , ThenPart from FormulaTable ft where ft.FormulaType='Formula'";
        DataTable searchnreplace = DataBaseMethods.ExecuteSelectQuery(key_value);

        bool found;
        do
        {
            found = false;
            for (int i = 0; i < searchnreplace.Rows.Count; ++i)
            //foreach (DataRow dr in searchnreplace.Rows)
            {
                DataRow dr = searchnreplace.Rows[i];
                if (output.Contains(dr[0].ToString()))
                {
                    output = output.Replace(dr[0].ToString(), dr[1].ToString());
                    output = output.Replace("@", " ").Replace("\r\n", "").Replace("\"", "'"); // for recursive replacements
                    found = true;
                }
            }
        } while (found);

        //for sql defined functions
        output = output.Replace("$", " ");
        return output;
    }

    private void getSalesData(DateTime[] dates)
    {
        string selectQuery = "SELECT [TransId], [EntryStatus], [AgentId], [TransDate], [CarValue], [CityCode], [Model], ";
        selectQuery += " [Manufacturer], [Scheme], [Description], [IntRate], [CarType], [LoanAmount], [Tenure] FROM [CarSalesData] ";
        selectQuery += " where [EntryStatus] not in ('Rejected','Adjusted')";
        salesdata = DataBaseMethods.ExecuteSelectQuery(selectQuery);
    }

    private DataTable getSubRules(int rule)
    {
        DataTable subRuleList = new DataTable();

        string selectQ = "select FormulaTable.ID, FormulaTable.FormulaName from FormulaTable, RuleRelations ";
        selectQ += "where RuleRelations.SubRuleId = FormulaTable.ID AND RuleRelations.RuleId = '" + rule.ToString() + "' ORDER BY RuleRelations.Priority";
        subRuleList = DataBaseMethods.ExecuteSelectQuery(selectQ);

        return subRuleList;
    }

    private DateTime[] getDateRange(int rule, int cycle)
    {
        //retrieve dates
        string selectQ = " select (CONVERT(varchar,StartDate,01) + ',' + CONVERT(varchar,StartDate,01)) as dates from CycleStatus  ";
        selectQ += "where RuleId='" + rule + "' and CycleId='" + cycle + "'";
        string[] cycledate = DataBaseMethods.ExecuteScalar(selectQ).Split(',');

        //validate and convert to array
        DateTime[] dateRange = new DateTime[2];
        dateRange[0] = Convert.ToDateTime(cycledate[0]);
        dateRange[1] = Convert.ToDateTime(cycledate[1]);

        return dateRange;
    }

    private void fill_result(int rid)
    {
        DataTable dt = new DataTable();

        string query = "select rp.Comission,rp.CreatedOn,rd.RuleName,rd.RuleType,ag.AgentName,cs.StartDate,cs.EndDate from RulePayout rp inner join RuleDetails rd ";
        query += "on rp.RuleId=rd.RuleID inner join Agents ag on rp.AgentId=ag.AgentId inner join CycleStatus cs ";
        query += "on rp.CycleId=cs.CycleId where rp.RuleId=" + RuleID + " and cs.CycleId=" + CycleID + " order by AgentName";


        dt = DataBaseMethods.ExecuteSelectQuery(query);

        if (dt != null && dt.Rows.Count > 0)
        {
            gv_result.DataSource = dt;
            gv_result.DataBind();

            lblCycle.Text = Convert.ToDateTime(dt.Rows[0]["StartDate"].ToString()).ToString("dd-MMM-yyyy") + "  " + Convert.ToDateTime(dt.Rows[0]["EndDate"].ToString()).ToString("dd-MMM-yyyy");
            lblruleName.Text = dt.Rows[0]["RuleName"].ToString();
            lblruleType.Text = dt.Rows[0]["RuleType"].ToString();
        }
        else
        {
            lblCycle.Text = "";
            lblruleName.Text = "";
            lblruleType.Text = "";

            btn_Export.Visible = false;

        }
    }
}
